package com.yang.test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.demodis.dao.GroupDaoI;
import com.demodis.dao.UserDaoI;
import com.demodis.dao.impl.GroupDaoImpl;
import com.demodis.dao.impl.UserDaoImpl;
import com.demodis.domain.TblGroup;
import com.demodis.domain.TblUser;
import com.demodis.util.Encrypt;

public class OpUserPwd {
	Map<String,String> map = new HashMap<String, String>();

	/**
	 * @description 生成固定长度的密码
	 * @param pwdLen
	 * @return
	 */
	public String getRandPwd(int pwdLen) {
		final int maxNum = 36;
		int i;
		int count = 0;
		char[] str = { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k',
				'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w',
				'x', 'y', 'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
		StringBuffer pwd = new StringBuffer("");
		Random r = new Random();
		while (count < pwdLen) {
			i = Math.abs(r.nextInt(maxNum));
			if (i >= 0 && i < str.length) {
				pwd.append(str[i]);
				count++;
			}
		}
		return pwd.toString();
	}

	public void getExcelCell() {
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					"d:/workbook.xls"));
			HSSFWorkbook wb = new HSSFWorkbook(fs);

			HSSFSheet sheet = wb.getSheetAt(0);

			//read rows cell
			HSSFRow row = sheet.getRow(0);
			int colNum = row.getPhysicalNumberOfCells();
			String[] title = new String[colNum];
			for (int i = 0; i < colNum; i++) {
				HSSFCell cell = row.getCell((short) i);
				title[i] = cell.getStringCellValue();
			}

			List<TblUser> userList = new ArrayList<TblUser>();
			//read columns cell
			int rowNum = sheet.getLastRowNum(); 
		     for (int i = 1; i <= rowNum; i++) {  
		    	    HSSFRow row1 = sheet.getRow(i);  
		    	    
		            HSSFCell cel1 = row1.getCell(0);
		            HSSFCell cel2 = row1.getCell(1);
		            HSSFCell celGroup = row1.getCell(2);
		            HSSFCell cel3 = row1.getCell(3);
		            HSSFCell cel4 = row1.getCell(4);
		            
		            String groupName = celGroup.getStringCellValue();
		            if(map.get(groupName) == null) {
		            	map.put(groupName, "groupName");
		            }
		            
		            cel3.setCellValue(getRandPwd(6));
		            cel4.setCellValue(Encrypt.e(cel3.getStringCellValue()));
		            
		            TblUser user = new TblUser();
		            user.setUsername(cel1.getStringCellValue());
		            user.setUserRealName(cel2.getStringCellValue());
		            user.setGroupName(celGroup.getStringCellValue());
		            user.setPassword(cel4.getStringCellValue());
		            userList.add(user);
		            
		           // System.out.println(cel1.getStringCellValue()+"-----");
		          //  System.out.println(cel2.getStringCellValue()+"*****");
		        //   System.out.println(cel3.getStringCellValue()+"*****");
		           // System.out.println(cel4.getStringCellValue()+"*****");
		        } 
			GroupDaoI groupDao = new GroupDaoImpl();
			TblGroup group = new TblGroup();
			Set<Map.Entry<String, String>> set = map.entrySet();
			for (Iterator<Map.Entry<String, String>> it = set.iterator(); it.hasNext();) {
				Map.Entry<String, String> entry = (Map.Entry<String, String>) it.next();
				group.setGroupName(entry.getKey());
				groupDao.save(group);
				System.out.println(entry.getValue()+"  "+entry.getKey());
			}
			Map<String,Integer> map = new HashMap<String, Integer>();
			List<TblGroup> list= groupDao.getGroup("");
			for(TblGroup g : list) {
				map.put(g.getGroupName(), g.getGroupId());
			}
			
			UserDaoI userDao = new UserDaoImpl();
			for(TblUser user : userList) {
				user.setGroupId(map.get(user.getGroupName()));
				userDao.save(user);
			}
			
			
			FileOutputStream fileOut = new FileOutputStream("d:/workbook1.xls");
			wb.write(fileOut);
			fileOut.close();
			System.out.println("ouver......");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// write the pwd int the cell

	public void writeExcelCell() {
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					"workbook.xls"));
			HSSFWorkbook wb = new HSSFWorkbook(fs);

			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFRow row = sheet.getRow(0);
			HSSFCell cell = row.getCell((short) 2);
			cell.setCellValue("a test");

			FileOutputStream fileOut = new FileOutputStream("d:/workbook1.xls");

			wb.write(fileOut);

			fileOut.close();
		} catch (Exception ex) {
			ex.printStackTrace();

		}
	}

	// test

	public static void main(String[] args) {
		OpUserPwd op = new OpUserPwd();
		// for(int i=0;i<100;i++){
		// String ranPwd = op.getRandPwd(5);
		// System.out.println(ranPwd);
		// }

		op.getExcelCell();
		// op.writeExcelCell();
	}

}
